﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace StuMana.Services
{
    class ExcelBase
    {
        private Microsoft.Office.Interop.Excel.Application app = null; 
        private Microsoft.Office.Interop.Excel.Workbook workbook = null; 
        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null; 
        private Microsoft.Office.Interop.Excel.Range workSheet_range = null; 
        
        SaveFileDialog saveFileDialog = new SaveFileDialog();
        object misValue = System.Reflection.Missing.Value;

        public ExcelBase() 
        { 
            createDoc();

            
            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel File To";
        } 
   
        public void createDoc() 
        { 
            try 
            { 
                app = new Microsoft.Office.Interop.Excel.Application();
                app.DisplayAlerts = false;
               // app.Visible = true;
                workbook = app.Workbooks.Add(1);                   
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];                
            } 
            catch (Exception e) 
            { 
                MessageBox.Show(e.Message,"Error"); 
            } 
            finally 
            { 
            }
        }

        public void InsertData(ExcelBE be) 
        { 
            worksheet.Cells[be.Row, be.Col] = be.Text;            
            workSheet_range = worksheet.get_Range(be.StartCell, be.EndCell); 
            workSheet_range.MergeCells = be.IsMerge; 
            workSheet_range.Interior.Color = GetColorValue(be.InteriorColor); 
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); 
            workSheet_range.ColumnWidth = be.Size; 
            workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? 
            System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb(); 
            workSheet_range.NumberFormat = be.Formart; 
        }

        public string getlocation()
        {                                              

            if (this.saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                return this.saveFileDialog.FileName;               
            }
            return null;
        }

        public void savefile()
        {
            try
            {
                workbook.SaveAs(this.saveFileDialog.FileName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                workbook.Close(true, misValue, misValue);
                app.Quit();
            }
            catch(Exception es) {
                MessageBox.Show(es.Message,"Excel Error");
            }
        }

        private int GetColorValue(string interiorColor) 
        {
            switch (interiorColor) 
            { 
                case "YELLOW": 
                    return System.Drawing.Color.Yellow.ToArgb(); 
                case "GRAY": 
                    return System.Drawing.Color.Gray.ToArgb(); 
                case "GAINSBORO": 
                    return System.Drawing.Color.Gainsboro.ToArgb(); 
                case "Turquoise": 
                    return System.Drawing.Color.Turquoise.ToArgb(); 
                case "PeachPuff": 
                    return System.Drawing.Color.PeachPuff.ToArgb(); 
   
                default: 
                    return System.Drawing.Color.White.ToArgb(); 
            }
        }
        
        // release object
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        //release
        ~ExcelBase()
        {
            //MessageBox.Show("restructor of ExcelBase");
            releaseObject(worksheet);
            releaseObject(workbook);
            releaseObject(app);
        }
    }
}